package edu.calpoly.csc.coursebook.util.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.Set;
import java.util.Map.Entry;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;

import edu.calpoly.csc.coursebook.business.model.Course;
import edu.calpoly.csc.coursebook.util.KeywordExtractor;

public class CategoryTablePopulator {
	KeywordExtractor categoryExtractor;

	public CategoryTablePopulator() {
		categoryExtractor = WordNetKeywordExtractor.getInstance();
	}

	public void populateCategoryTable(Collection<Course> courses) 
	throws SQLException, IOException {
		HashMap<String, Double> wordnetKeywords = new HashMap<String, Double>();
		HashMap<String, Double> temp = new HashMap<String, Double>();
		String query = "";
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cbdb", "coursebook", "courseb00k");
		Statement stmt = conn.createStatement();
		ResultSet rSet = null;
		long errors = 0;
		for(Course c:courses) {
			temp = (categoryExtractor.extractKeywords(c.getTitle()));

			Set<Entry<String, Double>> keywords = temp.entrySet();
			for (Entry<String, Double> entry : keywords) {
				System.out.println("title word: " + entry.getKey());
				wordnetKeywords.put(entry.getKey(), 1.0);
			}

			temp = categoryExtractor.extractKeywords(c.getDescription());

			double threshold = 1.000/(temp.size()*2.0);
			double weight = 1;

			keywords = temp.entrySet();
			for (Entry<String, Double> entry : keywords) {
				weight = weight-threshold;
				System.out.println("adding word = " + entry.getKey());
				if(!wordnetKeywords.containsKey(entry.getKey()))
					wordnetKeywords.put(entry.getKey(), weight);
			}

			keywords = wordnetKeywords.entrySet();
			for(Entry<String, Double> entry : keywords) 
			{
				String s;

				/* clean up the string a little bit */
				s = (entry.getKey()).toLowerCase();

				/* wrap everything in a try because there could be errors... */
				try
				{
					query = "insert into category (keyword) values('" + s + "')";
					try
					{
						stmt.execute(query);
					}
					catch (MySQLIntegrityConstraintViolationException sqle)
					{
						/* it's okay if happens */
						System.out.println(sqle.getMessage());
						String msg = sqle.getMessage();
						if (!msg.startsWith("Duplicate entry")) 
						{
							throw sqle;
						}
					}
					//rSet = stmt.executeQuery(query);

					query = "select id, keyword from category where keyword ='" + s + "'";
					rSet = stmt.executeQuery(query);
					rSet.next();

					query = "insert into categories_course (category, code, relevance) values ('" + 
					rSet.getString("id") + "','" + c.getCode() + "', " + 
					wordnetKeywords.get(rSet.getString("keyword")) + ")";

					stmt.execute(query);
					//rSet = stmt.executeQuery(query);
				}
				catch(Exception e)
				{
					errors += 1;
				}
			}
			wordnetKeywords.clear();
		}
	}
}
